package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.pojo.Member;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface MemberMapper extends BaseMapper<Member> {
    @Select("select * from t_member where PHONENUMBER = #{telephone}")
    Member findMemeberBytelephone(@Param("telephone") String telephone);

    @Select("select count(1) from t_member where REGTIME = #{today}")
    int getTodayNewMember(@Param("today") String today);
    @Select("select count(1) from t_member where REGTIME between #{thisWeekMonday} and #{thisWeekSunday}")
    int getThisWeekNewMember(@Param("thisWeekMonday") String thisWeekMonday, @Param("thisWeekSunday") String thisWeekSunday);

    @Select("select count(1) from t_member")
    int gettotalMember();
    @Select("select count(1) from t_member where REGTIME between #{firstDayOfMonth} and #{lastDayOfMonth}")
    int getthisMonthNewMember(@Param("firstDayOfMonth") String firstDayOfMonth, @Param("lastDayOfMonth") String lastDayOfMonth);

    @Select("select s.SEX as 'name',count(s.SEX) as 'value' from t_member s GROUP BY SEX;")
    List<Map> getMemberPercentageBySex();

    @Select("Select\n" +
            " Sum(Case When 年龄 <=20 Then 1 Else 0 End) As '[0-20岁(人)]',\n" +
            " Sum(Case When 年龄 Between 21 And 40 Then 1 Else 0 End) As '[21-40岁(人)]',\n" +
            " Sum(Case When 年龄 Between 41 And 60 Then 1 Else 0 End) As '[41-60岁(人)]',\n" +
            " Sum(Case When 年龄 >=61 Then 1 Else 0 End) As '[60岁以上(人)]' \n" +
            " From\n" +
            " (\n" +
            " SELECT *, ROUND(DATEDIFF(CURDATE(), BIRTHDAY)/365.2422)  AS '年龄' FROM t_member\n" +
            " ) s ")
    List<Map> getMemberAgeGroup();
}
